import pymssql
import pymysql
from datetime import datetime

# 配置连接信息
server_name = 'DESKTOP\\SQLSERVER'
database_name = '测试'
username = 'sa'
password = '123456'

# 连接 SQL Server
try:
    conn = pymssql.connect(server=server_name, user=username, password=password, database=database_name)
    print("连接SQL Server成功")
except Exception as e:
    print("连接SQL Server失败:", e)
    exit()

# 因为客户那边所有设备读取到数据然后插入到SQLsever需要大概十多秒的时间，所以我们需要延迟查询，在任务计划程序中设置当前分钟的30秒时间做查询

# 准备查询
query1 = f"SELECT * FROM T_FQ_MIN WHERE T1 >= '{datetime.now().strftime('%Y-%m-%d %H:%M')}:00' AND T1 <= '{datetime.now().strftime('%Y-%m-%d %H:%M')}:59'"
query2 = f"SELECT * FROM T_FS_MIN WHERE T1 >= '{datetime.now().strftime('%Y-%m-%d %H:%M')}:00' AND T1 <= '{datetime.now().strftime('%Y-%m-%d %H:%M')}:59'"

print(query1)
print(query2)


# 执行查询
cursor = conn.cursor(as_dict=True)
cursor.execute(query1)
F_FQ_MIN = cursor.fetchall()

cursor.execute(query2)
F_FS_MIN = cursor.fetchall()


# 释放资源和关闭连接
cursor.close()
conn.close()

# MySQL连接信息
mysql_ip = 'localhost'
mysql_username = 'root'
mysql_password = '123456'
mysql_dbname = 'test'

# 连接 MySQL
try:
    mysql_conn = pymysql.connect(host=mysql_ip, user=mysql_username, password=mysql_password, database=mysql_dbname)
    print("连接MySQL成功")
except Exception as e:
    print("连接MySQL失败:", e)
    exit()

mysql_cursor = mysql_conn.cursor()

# 处理 F_FQ_MIN 数据，MIN表是分钟数据表，将每分钟读取到的数据INSERT
for value in F_FQ_MIN:
    # 格式化时间字段
    value['T1'] = value['T1'].strftime("%Y-%m-%d %H:%M:%S")

    # 构建插入 SQL 语句
    sql = f"""
    INSERT IGNORE INTO t_fq_min (AID, BID, WRWID, T1, S1, VMAX, VAVG, VMIN, PFL, ZSAVG, ZSMAX, ZSMIN)
    VALUES (
        {value['AID']}, 
        {value['BID']}, 
        '{value['WRWID']}',
        '{value['T1']}', 
        {f"'{value['S1']}'" if value['S1'] else 'NULL'},
        {value['VMAX']},
        {value['VAVG']}, 
        {value['VMIN']}, 
        {value['PFL']}, 
        {value['ZSAVG']}, 
        {value['ZSMAX']}, 
        {value['ZSMIN']}
    )
    """

    print(sql)

    try:
        mysql_cursor.execute(sql)
        mysql_conn.commit()
        print("T_FQ_MIN 插入成功")
    except Exception as e:
        print("Error:", e)


# 处理 F_FS_MIN 数据，MIN表是分钟表，将每分钟读取到的数据INSERT
for value in F_FS_MIN:
    # 格式化时间字段
    value['T1'] = value['T1'].strftime("%Y-%m-%d %H:%M:%S")

    # 构建插入 SQL 语句
    sql = f"""
    INSERT IGNORE INTO t_fs_min (FID, SID, RFID, T1, S1, MAXV, AVGV, MINV, LDF, SAVG, SMAX, SMIN)
    VALUES (
        {value['FID']},  
        '{value['SID']}',  
        '{value['RFID']}',  
        '{value['T1']}',  
        {f"'{value['S1']}'" if value['S1'] else 'NULL'}, 
        {value['MAXV']}, 
        {value['AVGV']}, 
        {value['MINV']}, 
        {value['LDF']},
        {value['SAVG']},  
        {value['SMAX']},  
        {value['SMIN']} 
    )
    """
    print(sql)
    try:
        mysql_cursor.execute(sql)
        mysql_conn.commit()
        print("T_FS_MIN 插入成功")
    except Exception as e:
        print("Error:", e)


# 关闭连接
mysql_cursor.close()
mysql_conn.close()

